package com.njust.Util;

import com.alibaba.excel.EasyExcel;
import com.njust.domain.vo.TestPageQueryPersonVO;
import com.njust.domain.vo.TrainPageQueryProjectVO;
import com.njust.domain.vo.TrainPageQueryVO;
import com.njust.mapper.PersonnelCapabilitieMapper;
import com.njust.mapper.TestPersonMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Component;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
@Component
@Slf4j
public class ExcelUtil {
    /**
     * excel文件导入处理
     * @param clazz 具体要转化的类class文件
     * @param inputStream excel流文件
     * @param name excel表中具体导入的表名称
     * @return
     * @param <T>
     */
    @Autowired
    private PersonnelCapabilitieMapper capabilitieMapper;

    @Autowired
    private TestPersonMapper testPersonMapper;

    public static <T> List<T> readExcel(Class<T> clazz, InputStream inputStream, String name) {
        List<T> data = EasyExcel
                .read(inputStream)
                .head(clazz)
                .sheet(name)
                .headRowNumber(1)
                .doReadSync();
        return data;
    }

    /**
     * 导出人员授权清单
     * @param response 响应
     * @param project_id 项目id
     */
    public void exportPersonExcel(HttpServletResponse response, int project_id) {

        //准备导出数据
        List<TrainPageQueryVO> trainPageQueryVOList = capabilitieMapper.selectAllPage(project_id);

        try {
            // 读取Excel模板文件
            FileInputStream inputStream = new FileInputStream("src/main/resources/templates/清单.xlsx");
            Workbook workbook = new XSSFWorkbook(inputStream);
            inputStream.close();
            // 标记是否找到指定名称的Sheet
            boolean sheetFound = false;

            // 获取模板中的Sheet页数量
            int sheetCount = workbook.getNumberOfSheets();

            // 遍历所有Sheet，找到指定名称的Sheet并删除其他Sheet
            for (int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet.getSheetName().equals("人员能力清单")) {
                    sheetFound = true;
                } else {
                    workbook.removeSheetAt(i);
                    i--;
                    sheetCount--;
                }
            }

            // 如果没有找到指定名称的Sheet，抛出异常
            if (!sheetFound) {
                throw new IllegalArgumentException("未在模板文件中找到名为'人员能力清单'的Sheet。");
            }


            // 获取模板中的Sheet页
            Sheet sheet = workbook.getSheet("人员能力清单");

            // 清空模板中的示例数据（从第二行开始，第一行是表头）
            int lastRowNum = sheet.getLastRowNum();
            for (int i = lastRowNum; i >= 1; i--) {
                Row row = sheet.getRow(i);
                if (row!= null) {
                    sheet.removeRow(row);
                }
            }

            // 遍历查询到的数据库数据列表，将数据填充到Excel表格中
            int rowIndex = 1;
            for (TrainPageQueryVO trainPageQueryVO : trainPageQueryVOList) {
                Row dataRow = sheet.createRow(rowIndex++);

                // 根据实体类属性将数据填充到对应的单元格
                dataRow.createCell(0).setCellValue(trainPageQueryVO.getUName());
                dataRow.createCell(1).setCellValue(trainPageQueryVO.getCategory());
                dataRow.createCell(2).setCellValue(trainPageQueryVO.getProject_name());
                dataRow.createCell(3).setCellValue(trainPageQueryVO.getProduct_name());
                dataRow.createCell(4).setCellValue(trainPageQueryVO.getStandard_number());
                dataRow.createCell(5).setCellValue(trainPageQueryVO.getTraining_completion());
                dataRow.createCell(6).setCellValue(trainPageQueryVO.getTraining_cert_link());
                dataRow.createCell(7).setCellValue(trainPageQueryVO.getAssessment_completion());
                dataRow.createCell(8).setCellValue(trainPageQueryVO.getVideo_link());
                dataRow.createCell(9).setCellValue(trainPageQueryVO.getAuthorization_cert_link());

            }

            // 将填充好数据的Excel文件保存到本地
            //FileOutputStream outputStream = new FileOutputStream("人员能力清单.xlsx");
            ServletOutputStream outputStream=response.getOutputStream();
            String encodedFileName = URLEncoder.encode("人员授权清单.xlsx", "UTF-8");
            response.setHeader("Content-disposition","attachment;filename=\""+encodedFileName+"\"");
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
            log.info("数据已成功导出到Excel文件！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出项目授权清单 作为响应附件
     * @param response 响应
     * @param project_id 项目id
     */
    public void exportProjectExcel(HttpServletResponse response, int project_id) {

        //准备导出数据
        List<TrainPageQueryProjectVO> trainPageQueryProjectVOList = capabilitieMapper.projectSelectAllPage(project_id);

        try {
            // 读取Excel模板文件
            FileInputStream inputStream = new FileInputStream("src/main/resources/templates/清单.xlsx");
            Workbook workbook = new XSSFWorkbook(inputStream);
            inputStream.close();
            // 标记是否找到指定名称的Sheet
            boolean sheetFound = false;

            // 获取模板中的Sheet页数量
            int sheetCount = workbook.getNumberOfSheets();

            // 遍历所有Sheet，找到指定名称的Sheet并删除其他Sheet
            for (int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet.getSheetName().equals("人员能力清单")) {
                    sheetFound = true;
                } else {
                    workbook.removeSheetAt(i);
                    i--;
                    sheetCount--;
                }
            }

            // 如果没有找到指定名称的Sheet，抛出异常
            if (!sheetFound) {
                throw new IllegalArgumentException("未在模板文件中找到名为'人员能力清单'的Sheet。");
            }


            // 获取模板中的Sheet页
            Sheet sheet = workbook.getSheet("人员能力清单");

            // 清空模板中的示例数据（从第二行开始，第一行是表头）
            int lastRowNum = sheet.getLastRowNum();
            for (int i = lastRowNum; i >= 1; i--) {
                Row row = sheet.getRow(i);
                if (row!= null) {
                    sheet.removeRow(row);
                }
            }

            // 遍历查询到的数据库数据列表，将数据填充到Excel表格中
            int rowIndex = 1;
            for (TrainPageQueryProjectVO trainPageQueryProjectVO : trainPageQueryProjectVOList) {
                Row dataRow = sheet.createRow(rowIndex++);

                // 根据实体类属性将数据填充到对应的单元格
                dataRow.createCell(0).setCellValue(trainPageQueryProjectVO.getUName());
                dataRow.createCell(1).setCellValue(trainPageQueryProjectVO.getCategory());
                dataRow.createCell(2).setCellValue(trainPageQueryProjectVO.getProject_name());
                dataRow.createCell(3).setCellValue(trainPageQueryProjectVO.getProduct_name());
                dataRow.createCell(4).setCellValue(trainPageQueryProjectVO.getStandard_number());

            }

            // 将填充好数据的Excel文件保存到本地
            //FileOutputStream outputStream = new FileOutputStream("人员能力清单.xlsx");
            ServletOutputStream outputStream=response.getOutputStream();
            String encodedFileName = URLEncoder.encode("项目授权清单.xlsx", "UTF-8");
            response.setHeader("Content-disposition","attachment;filename=\""+encodedFileName+"\"");
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
            log.info("数据已成功导出到Excel文件！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public void exportTestExcel(HttpServletResponse response, int project_id) {

        //准备导出数据
        List<TestPageQueryPersonVO> testPageQueryPersonVOList = testPersonMapper.pageQueryById(project_id);

        try {
            // 读取Excel模板文件
            FileInputStream inputStream = new FileInputStream("src/main/resources/templates/清单.xlsx");
            Workbook workbook = new XSSFWorkbook(inputStream);
            inputStream.close();
            // 标记是否找到指定名称的Sheet
            boolean sheetFound = false;

            // 获取模板中的Sheet页数量
            int sheetCount = workbook.getNumberOfSheets();

            // 遍历所有Sheet，找到指定名称的Sheet并删除其他Sheet
            for (int i = 0; i < sheetCount; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet.getSheetName().equals("比对测试申请清单")) {
                    sheetFound = true;
                } else {
                    workbook.removeSheetAt(i);
                    i--;
                    sheetCount--;
                }
            }

            // 如果没有找到指定名称的Sheet，抛出异常
            if (!sheetFound) {
                throw new IllegalArgumentException("未在模板文件中找到名为'比对测试申请清单'的Sheet。");
            }


            // 获取模板中的Sheet页
            Sheet sheet = workbook.getSheet("比对测试申请清单");

            // 清空模板中的示例数据（从第二行开始，第一行是表头）
            int lastRowNum = sheet.getLastRowNum();
            for (int i = lastRowNum; i >= 1; i--) {
                Row row = sheet.getRow(i);
                if (row!= null) {
                    sheet.removeRow(row);
                }
            }

            // 遍历查询到的数据库数据列表，将数据填充到Excel表格中
            int rowIndex = 1;
            for (TestPageQueryPersonVO testPageQueryPersonVO : testPageQueryPersonVOList) {
                Row dataRow = sheet.createRow(rowIndex++);

                // 根据实体类属性将数据填充到对应的单元格
                dataRow.createCell(0).setCellValue(testPageQueryPersonVO.getUName());
                dataRow.createCell(1).setCellValue(testPageQueryPersonVO.getCategory());
                dataRow.createCell(2).setCellValue(testPageQueryPersonVO.getProject_name());
                dataRow.createCell(3).setCellValue(testPageQueryPersonVO.getProduct_name());
                dataRow.createCell(4).setCellValue(testPageQueryPersonVO.getStandard_number());
                dataRow.createCell(5).setCellValue(testPageQueryPersonVO.getTest_time());
                dataRow.createCell(6).setCellValue(testPageQueryPersonVO.getCompletion_date_time());

            }

            // 将填充好数据的Excel文件保存到本地
            //FileOutputStream outputStream = new FileOutputStream("人员能力清单.xlsx");
            ServletOutputStream outputStream=response.getOutputStream();
            String encodedFileName = URLEncoder.encode("比对测试申请清单.xlsx", "UTF-8");
            response.setHeader("Content-disposition","attachment;filename=\""+encodedFileName+"\"");
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
            log.info("数据已成功导出到Excel文件！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    }

